﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using DataHelper;
namespace DAL
{
    public class SetsItemDao
    {
        //根据汉语名字获取字段
        public string GetWageItem(string setsID,string itemName)
        {
            string wageItem = "";
            DataTable dt = GetItemDict(setsID, "all");//SQLHelper.ExecuteSqlGetDataSet("select WageItem,itemName from Wage_SetsItem where  SetsID=" + setsID + "   order by WageItem ASC").Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (itemName == dt.Rows[i]["itemName"].ToString())
                {
                    wageItem = dt.Rows[i]["WageItem"].ToString();
                    break;
                }
            }
            return wageItem;
        }

        //根据字段名获取汉语
        public string GetItemName(string setsID, string wageItem)
        {
            string itemName = "";
            DataTable dt = GetItemDict(setsID, "all");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (wageItem == dt.Rows[i]["WageItem"].ToString())
                {
                    itemName = dt.Rows[i]["ItemName"].ToString();
                    break;
                }
            }
            return itemName;
        }
        //查找最近一个没有使用的编号
        public string GetFirstItem(string setsID)
        {
            DataTable dt = SQLHelper.ExecuteSqlGetDataSet("select top 1 WageItem from Wage_Item where WageItem not in (select WageItem from Wage_SetsItem where SetsID=" + setsID + ")  order by WageItem ASC").Tables[0];
            if (dt.Rows.Count > 0)
                return dt.Rows[0]["WageItem"].ToString().Trim();
            else
                return "";
        }

        public DataTable GetItemDict(string setsID, string fieldRange)
        {
            //
            if (fieldRange == "part")
                return SQLHelper.ExecuteSqlGetDrToDt("select WageItem,itemName,InitValue from Wage_SetsItem where  SetsID=" + setsID + "  and ItemType='输入项' order by AbsIndex ASC");
            else//all
                return SQLHelper.ExecuteSqlGetDrToDt("select WageItem,itemName,InitValue from Wage_SetsItem where  SetsID=" + setsID + "   order by AbsIndex ASC");

        }
        //获取该帐套下要显示哪些字段
        //获取有顺序的字段
        public string GetFieldsDic(string setsID)
        {
            Dictionary<string, string> fieldsDic = new Dictionary<string, string>();
            DataTable dt = GetItemDict(setsID, "all");//SQLHelper.ExecuteSqlGetDataSet("select WageItem,itemName from Wage_SetsItem where  SetsID=" + setsID + "   order by WageItem ASC").Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                fieldsDic.Add(dt.Rows[i]["WageItem"].ToString(), dt.Rows[i]["itemName"].ToString());
            }
            StringBuilder fields = new StringBuilder();
            //(Case When a.FAddWorkH2=0 Then Null Else a.FAddWorkH2 End) As FAddWorkH2  cast(   as   float)
            foreach (KeyValuePair<string, string> kvp in fieldsDic)
            {
                fields.Append(",(Case When " + kvp.Key + "=0   Then Null Else cast(round(" + kvp.Key + ",1,1) as   float) End) as " + kvp.Value);
            }
            return fields.ToString();
        }
        //必须紧跟上一个使用，用来求和
        public string GetFieldsSumDic(string setsID)
        {
            Dictionary<string, string> fieldsDic = new Dictionary<string, string>();
            DataTable dt = GetItemDict(setsID, "all");//SQLHelper.ExecuteSqlGetDataSet("select WageItem,itemName from Wage_SetsItem where  SetsID=" + setsID + "   order by WageItem ASC").Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                fieldsDic.Add(dt.Rows[i]["WageItem"].ToString(), dt.Rows[i]["itemName"].ToString());
            }
            StringBuilder fields = new StringBuilder();
            //(Case When a.FAddWorkH2=0 Then Null Else a.FAddWorkH2 End) As FAddWorkH2  cast(   as   float)
            foreach (KeyValuePair<string, string> kvp in fieldsDic)
            {
                //round(aa,1,1)保留小数点1位并四舍五入 round(aa,2,0) 保留两位并不四舍五入
                fields.Append(",SUM(Case When " + kvp.Key + "=0   Then Null Else cast(round(" + kvp.Key + ",1,1)   as   float) End) as " + kvp.Value);
            }
            return fields.ToString();
        }
        //获取该帐套下要显示哪些字段 
        //获得没有顺序的hashtable
    
        public Hashtable GetFieldsHashTable(string setsID)
        {           
            Hashtable ht = new Hashtable();
            DataTable dt = GetItemDict(setsID, "all");//SQLHelper.ExecuteSqlGetDataSet("select WageItem,itemName from Wage_SetsItem where  SetsID=" + setsID + "   order by WageItem ASC").Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                ht.Add(dt.Rows[i]["WageItem"].ToString(), dt.Rows[i]["itemName"].ToString());
            }
            return ht;
        }

        //获取列表
        public DataTable GetItemList(string accountSetID)
        {
            string strSQL = string.Empty;
            if (string.IsNullOrWhiteSpace(accountSetID))
                strSQL = "select * from Wage_SetsItem order by AbsIndex ASC";
            else
                strSQL = "select * from Wage_SetsItem where SetsID=" + accountSetID + " order by AbsIndex ASC";
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
        }


    }
}
